package database

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"gorm.io/gorm"
)

// DatabaseMetrics 数据库指标
type DatabaseMetrics struct {
	ConnectionPoolStats *sql.DBStats `json:"connection_pool_stats"`
	TableCount          int64        `json:"table_count"`
	DatabaseSize        string       `json:"database_size"`
	ActiveConnections   int          `json:"active_connections"`
	SlowQueries         []SlowQuery  `json:"slow_queries,omitempty"`
	Timestamp           time.Time    `json:"timestamp"`
}

// SlowQuery 慢查询信息
type SlowQuery struct {
	Query     string        `json:"query"`
	Duration  time.Duration `json:"duration"`
	Timestamp time.Time     `json:"timestamp"`
}

// HealthCheckResult 健康检查结果
type HealthCheckResult struct {
	Timestamp       time.Time     `json:"timestamp"`
	Status          string        `json:"status"` // healthy, degraded, unhealthy
	ResponseTime    time.Duration `json:"response_time"`
	DatabaseVersion string        `json:"database_version,omitempty"`
	ConnectionStats *sql.DBStats  `json:"connection_stats,omitempty"`
	Error           string        `json:"error,omitempty"`
}

// GetDatabaseMetrics 获取数据库指标
func GetDatabaseMetrics(db *gorm.DB) (*DatabaseMetrics, error) {
	connStats, err := GetConnectionStats(db)
	if err != nil {
		return nil, fmt.Errorf("failed to get connection stats: %w", err)
	}

	// 获取表数量
	var tableCount int64
	err = db.Raw(`
		SELECT COUNT(*) FROM information_schema.tables 
		WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
	`).Scan(&tableCount).Error
	if err != nil {
		return nil, fmt.Errorf("failed to get table count: %w", err)
	}

	// 获取数据库大小
	var dbSize string
	err = db.Raw("SELECT pg_size_pretty(pg_database_size(current_database()))").Scan(&dbSize).Error
	if err != nil {
		return nil, fmt.Errorf("failed to get database size: %w", err)
	}

	// 获取活跃连接数
	var activeConns int
	err = db.Raw("SELECT count(*) FROM pg_stat_activity WHERE state = 'active'").Scan(&activeConns).Error
	if err != nil {
		return nil, fmt.Errorf("failed to get active connections: %w", err)
	}

	return &DatabaseMetrics{
		ConnectionPoolStats: connStats,
		TableCount:          tableCount,
		DatabaseSize:        dbSize,
		ActiveConnections:   activeConns,
		Timestamp:           time.Now(),
	}, nil
}

// HealthCheckDetailed 详细健康检查
func HealthCheckDetailed(db *gorm.DB) (*HealthCheckResult, error) {
	result := &HealthCheckResult{
		Timestamp: time.Now(),
		Status:    "healthy",
	}

	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	sqlDB, err := db.DB()
	if err != nil {
		result.Status = "unhealthy"
		result.Error = fmt.Sprintf("failed to get sql.DB: %v", err)
		return result, nil
	}

	// 测试连接
	start := time.Now()
	if err := sqlDB.PingContext(ctx); err != nil {
		result.Status = "unhealthy"
		result.Error = fmt.Sprintf("database ping failed: %v", err)
		return result, nil
	}
	result.ResponseTime = time.Since(start)

	// 获取连接统计
	if stats, err := GetConnectionStats(db); err == nil {
		result.ConnectionStats = stats
	}

	// 测试简单查询
	var version string
	if err := db.WithContext(ctx).Raw("SELECT version()").Scan(&version).Error; err != nil {
		result.Status = "degraded"
		result.Error = fmt.Sprintf("query test failed: %v", err)
	} else {
		result.DatabaseVersion = version
	}

	return result, nil
}

// GetSlowQueries 获取慢查询
func GetSlowQueries(db *gorm.DB, limit int) ([]SlowQuery, error) {
	var queries []SlowQuery

	// 这里可以根据具体数据库实现慢查询获取逻辑
	// PostgreSQL 示例（需要启用 pg_stat_statements 扩展）
	rows, err := db.Raw(`
		SELECT query, mean_time, calls, total_time
		FROM pg_stat_statements
		WHERE mean_time > 1000
		ORDER BY mean_time DESC
		LIMIT ?
	`, limit).Rows()

	if err != nil {
		return nil, fmt.Errorf("failed to get slow queries: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var query string
		var meanTime, calls, totalTime float64

		if err := rows.Scan(&query, &meanTime, &calls, &totalTime); err != nil {
			continue
		}

		queries = append(queries, SlowQuery{
			Query:     query,
			Duration:  time.Duration(meanTime) * time.Millisecond,
			Timestamp: time.Now(),
		})
	}

	return queries, nil
}

// GetTableSizes 获取表大小信息
func GetTableSizes(db *gorm.DB) ([]TableSizeInfo, error) {
	var tableSizes []TableSizeInfo

	rows, err := db.Raw(`
		SELECT 
			schemaname,
			tablename,
			pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
			pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
		FROM pg_tables
		WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
		ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
	`).Rows()

	if err != nil {
		return nil, fmt.Errorf("failed to get table sizes: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var schema, table, size string
		var sizeBytes int64

		if err := rows.Scan(&schema, &table, &size, &sizeBytes); err != nil {
			continue
		}

		tableSizes = append(tableSizes, TableSizeInfo{
			Schema:    schema,
			TableName: table,
			Size:      size,
			SizeBytes: sizeBytes,
		})
	}

	return tableSizes, nil
}

// TableSizeInfo 表大小信息
type TableSizeInfo struct {
	Schema    string `json:"schema"`
	TableName string `json:"table_name"`
	Size      string `json:"size"`
	SizeBytes int64  `json:"size_bytes"`
}
